package org.gen.code.gen.dao.impl;

import cn.hutool.core.util.StrUtil;
import org.gen.code.core.domain.GenTableColumn;
import org.gen.code.gen.dao.CodeDao;
import org.gen.code.gen.domain.ColumnInfo;
import org.gen.code.db.domain.DbConfig;
import org.gen.code.gen.domain.TableInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Repository
public class CodeDaoImpl implements CodeDao {

    Logger log = LoggerFactory.getLogger(CodeDaoImpl.class);

    @Override
    public void saveComment(TableInfo tableInfo, DbConfig dbConfig) {
//        Connection conn = getConnection(dbConfig);
//        try {
//            Statement stmt = conn.createStatement();
//            String strSql = "";
//            if (dbConfig.getUrl().indexOf("mysql") > 0) {
//                strSql = "ALTER TABLE " + tableInfo.getTableName() + " COMMENT '" + tableInfo.getComments() + "';";
//                stmt.executeUpdate(strSql);
//                //stmt.executeUpdate("use information_schema;");
//                for (ColumnInfo item : tableInfo.getListColumn()) {
//                    StringBuilder sb = new StringBuilder();
//                    sb.append("ALTER TABLE ");
//                    if (StrUtil.isEmpty(tableInfo.getTableName())) {
//                        continue;
//                    } else {
//                        sb.append(" " + tableInfo.getTableName() + " ");
//                    }
//                    sb.append(" MODIFY ");
//                    if (StrUtil.isEmpty(item.getColName())) {
//                        continue;
//                    } else {
//                        sb.append(" `" + item.getColName() + "` ");
//                    }
//                    sb.append(" " + item.getColType() + " ");
//                    if (!item.isNullable()) {
//                        sb.append(" NOT NULL ");
//                    }
//                    if (!StrUtil.isEmpty(item.getDefaultValue())) {
//                        if (item.getDefaultValue().equalsIgnoreCase("undefined") || hasChinese(item.getDefaultValue())) {
//                            sb.append(" DEFAULT '" + item.getDefaultValue() + "' ");
//                        } else {
//                            sb.append(" DEFAULT " + item.getDefaultValue() + " ");
//                        }
//                    }
//                    if (!StrUtil.isEmpty(item.getExtra())) {
//                        sb.append(" " + item.getExtra() + " ");
//                    }
//                    if (!StrUtil.isEmpty(item.getComments())) {
//                        sb.append(" COMMENT '" + item.getComments() + "' ;");
//                    }
//                    log.info("update mysql column, sql is : {}", sb.toString());
//                    stmt.executeUpdate(sb.toString());
//                }
//            } else {
//                strSql = "COMMENT ON TABLE " + tableInfo.getTableName() + " IS '#" + tableInfo.getComments() + "'";
//                stmt.executeUpdate(strSql);
//                for (ColumnInfo item : tableInfo.getListColumn()) {
//                    strSql = "COMMENT ON COLUMN " + tableInfo.getTableName() + "." + item.getColName() + " IS '" + item.getComments() + "'";
//                    stmt.executeUpdate(strSql);
//                }
//            }
//            if (stmt != null) {   // 关闭声明
//                try {
//                    stmt.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException("execute sql occer error", e);
//        } finally {
//            try {
//                conn.close();
//            } catch (Exception e) {
//                throw new RuntimeException(e);
//            }
//        }
    }

    @Override
    public List<TableInfo> getAllTables(DbConfig dbConfig) {
        List<TableInfo> tableList = new ArrayList<TableInfo>();
        Connection conn = getConnection(dbConfig);
        try {
            Statement stmt = conn.createStatement();
            String strSql = "select table_name,TABLE_COMMENT from information_schema.tables where table_schema='" + dbConfig.getTableSchema() + "'";
            System.out.println(">>>>>>>>>>>>" + strSql);
            ResultSet rs = stmt.executeQuery(strSql);
            while (rs.next()) {
                TableInfo table = new TableInfo();
                table.setTableName(rs.getString(1));
                table.setComments(rs.getString(2));
                tableList.add(table);
            }
            if (stmt != null) {   // 关闭声明
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("execute sql occer error", e);
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }

        return tableList;
    }

    @Override
    public TableInfo getAllColumns(String tableName, DbConfig dbConfig) {
        TableInfo tableInfo = new TableInfo();
        tableInfo.setTableName(tableName);
        Connection conn = getConnection(dbConfig);
        try {
            Statement stmt = conn.createStatement();
            String strSql = "select TABLE_COMMENT from information_schema.tables where table_name='" + tableName + "' and table_schema='" + dbConfig.getTableSchema() + "'";
            ResultSet rs = stmt.executeQuery(strSql);
            while (rs.next()) {
                tableInfo.setComments(rs.getString(1));
            }
            //得到字段注解
            if (dbConfig.getUrl().indexOf("mysql") > 0) {
                strSql = "select column_name,column_comment,column_type,is_nullable,extra,column_default,column_key from Information_schema.columns where table_Name = '" + tableName + "' and table_schema='" + dbConfig.getTableSchema() + "'";
            } else {
                strSql = "select z.COLUMN_NAME,c.comments,z.data_type from user_tab_columns z,user_col_comments c where z.TABLE_NAME=c.table_name and z.COLUMN_NAME=c.column_name and z.Table_Name='" + tableName + "'";
            }
            List<GenTableColumn> colList = new ArrayList<>();
            rs = stmt.executeQuery(strSql);
            while (rs.next()) {
                GenTableColumn colInfo = new GenTableColumn();
                colInfo.setColumnName(rs.getString(1));
                colInfo.setColumnComment(rs.getString(2));
                if (dbConfig.getUrl().indexOf("mysql") > 0) {
                    colInfo.setColumnType(rs.getString(3));
//                    colInfo.setDefaultValue(rs.getString(6));
                    colInfo.setIsPk("0");
                    if ("PRI".equals(rs.getString(7))) {
                        colInfo.setIsPk("1");
                    }
//                    colInfo.setNullable("YES".equals(rs.getString(4)));
                } else {
//                    colInfo.setColType(rs.getString(3));
                }
//                if (!StrUtil.isEmpty(rs.getString(5))) {
//                    colInfo.setExtra(rs.getString(5));
//                } else {
//                    colInfo.setExtra("");
//                }
                colList.add(colInfo);
            }
            tableInfo.setListColumn(colList);

            if (stmt != null) {   // 关闭声明
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("execute sql occer error", e);
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }

        return tableInfo;
    }

    /* 获取数据库连接的函数*/
    private Connection getConnection(DbConfig dbConfig) {
        Connection con = null;  //创建用于连接数据库的Connection对象
        try {
            Class.forName(dbConfig.getDriver());// 加载Mysql数据驱动
            con = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUserName(), dbConfig.getPassword());// 创建数据连接

        } catch (Exception e) {
            System.out.println("数据库连接失败" + e.getMessage());
        }
        return con; //返回所建立的数据库连接
    }

    /* 获取数据库连接的函数*/
    @Override
    public String testConnection(DbConfig dbConfig) {
        Connection con = null;  //创建用于连接数据库的Connection对象
        try {
            Class.forName(dbConfig.getDriver());// 加载Mysql数据驱动
            con = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUserName(), dbConfig.getPassword());// 创建数据连接
            if (con == null) {
                return "数据库连接失败";
            }
        } catch (Exception e) {
            System.out.println("数据库连接失败：" + e.getMessage());
            return e.getMessage();
        }
        return null;
    }


    /**
     * 根据正则表达式判断字符是否为汉字
     * 字符串中包含汉字时返回true
     */
    public static boolean hasChinese(String value) {
        // 汉字的Unicode取值范围
        String regex = "[\u4e00-\u9fa5]";
        Pattern pattern = Pattern.compile(regex);
        Matcher match = pattern.matcher(value);
        return match.find();
    }


}
